CREATE OR REPLACE EDITIONABLE FUNCTION "SCOTT"."FNC_JUDGE_BY_C_P_GOLD_MA5" (p_date       in varchar2,
                                                           p_stock_code in varchar2)
  return number is
  /* 判断某一只股票在某一日是否收盘价金叉五日均线。如果是金叉，则返回1，如果不是金叉，则返回-1 */
  result number;
  -- 当前日期的收盘价
  v_current_close_price number;
  -- 当前日期的五日均线
  v_current_ma5 number;
  -- 前一日的收盘价
  v_previous_close_price number;
  -- 前一日的五日均线
  v_previous_ma5 number;
begin
  -- 当日的收盘价和MA5
  begin
    select std.close_price, std.ma5
      into v_current_close_price, v_current_ma5
      from stock_transaction_data_all std
     where std.code_ = p_stock_code
       and std.date_ = to_date(p_date, 'yyyy-mm-dd');
  exception
    when no_data_found then
      DBMS_OUTPUT.put_line('代码为【' || p_stock_code || '】的股票，
            在【' || p_date || '】没有收盘价和ma5');
      result := -1;
      return(result);
  end;

  -- 前一日的收盘价和ma5
  begin
    select std.close_price, std.ma5
      into v_previous_close_price, v_previous_ma5
      from stock_transaction_data_all std
     where std.code_ = p_stock_code
       and std.date_ =
           (select b.date_
              from (select *
                      from stock_transaction_data_all std2
                     where std2.code_ = p_stock_code
                       and std2.date_ < to_date(p_date, 'yyyy-mm-dd')
                     order by std2.date_ desc) b
             where rownum <= 1);
  exception
    when no_data_found then
      DBMS_OUTPUT.put_line('代码为【' || p_stock_code || '】的股票，
            在【' || p_date || '】的前一日没有收盘价和ma5');
      result := -1;
      return(result);
  end;

  -- 判断收盘价金叉五日均线是否成立
  if v_current_close_price >= v_current_ma5 and
     v_previous_close_price <= v_previous_ma5 then
    result := 1;
    return(result);
  else
    result := -1;
    return(result);
  end if;

end FNC_JUDGE_BY_C_P_GOLD_MA5;